# count_distinct_values.py
import pymysql

# --- 数据库连接配置 ---
host = 'tenant-2102622389-cn-guangzhou-public.bytehouse.volces.com'
port = 3306
database = 'strategy_cloud'
username = 'bytehouse'
password = 'gd3gjGTXTb:WBSEjA67dI'


# 要统计的表名
table_name = 'cmh_jcy_cmh_std_sales_phl_shard'

# 要统计的字段列表 (与 获取字段.py 中保持一致)
string_columns = [
    'years', 'quarters', 'months', 'region_type', 'region',
    'sort1', 'sort2', 'sort3', 'sort4', 'tym', 'pm', 'spm',
    'cj', 'rights', 'zx', 'otc_rx', 'jx', 'dx', 'take_way',
    'company_type', 'transfer_rat', 'vpd', 'base_drug', 'health_pro_type',
    'health_type_hist', 'zb_batch_num', 'consistency_evaluation',
    'chemical_drug_cr', 'drug_class', 'short_brand', 'pm_all', 'short_cj',
    'indication_type_name', 'gg1', 'big_jx', 'small_jx', 'first_generics',
    'dsc_import', 'element'
]

# 存储统计结果
distinct_counts = {}
# 用于累加所有字段的去重值总和
total_distinct_sum = 0

try:
    # 连接数据库
    # 创建数据库连接
    connection = pymysql.connect(
        host=host,
        port=int(port),
        user=username,
        password=password,
        database=database,
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    cursor = connection.cursor()

    print(f"成功连接到数据库: {database}")

    print(f"开始统计表 '{table_name}' 中指定字段的去重值数量...")

    # 遍历字段列表进行统计
    for col in string_columns:
        try:
            # 构建 SQL 查询语句，计算非空且非空字符串的去重值数量
            sql = f"SELECT COUNT(DISTINCT `{col}`) as count FROM `{table_name}` WHERE `{col}` IS NOT NULL AND `{col}` != ''"
            cursor.execute(sql)
            result = cursor.fetchone()
            count = result['count'] if result else 0
            distinct_counts[col] = count
            # 累加去重值数量
            if isinstance(count, int):
                total_distinct_sum += count
            print(f"- 字段 '{col}': {count} 个唯一值")
        except pymysql.Error as e:
            print(f"  统计字段 '{col}' 时出错: {e}")
            distinct_counts[col] = f"Error: {e}"
        except Exception as e:
            print(f"  统计字段 '{col}' 时发生未知错误: {e}")
            distinct_counts[col] = f"Unknown Error: {e}"


    print("\n--- 统计结果 ---")
    # 按数量降序排序
    # 定义排序键函数，处理可能的错误字符串
    def sort_key(item):
        key, value = item
        if isinstance(value, int):
            return value
        else:
            # 将错误信息排在最后
            return -1 
            
    sorted_counts = sorted(distinct_counts.items(), key=sort_key, reverse=True)
    
    for col, count in sorted_counts:
        print(f"{col}: {count}")

    # 打印所有字段去重值数量的总和
    print(f"\n--- 指定字段去重值总和 ---")
    print(f"Sum of Distinct Counts: {total_distinct_sum}")

    # 统计总记录数
    try:
        print("\n正在统计总记录数...")
        total_count_sql = f"SELECT COUNT(*) as total FROM `{table_name}`"
        cursor.execute(total_count_sql)
        total_result = cursor.fetchone()
        total_count = total_result['total'] if total_result else 0
        print(f"\n--- 表 '{table_name}' 总记录数 ---")
        print(f"Total Records: {total_count}")
    except pymysql.Error as e:
        print(f"  统计总记录数时出错: {e}")
    except Exception as e:
        print(f"  统计总记录数时发生未知错误: {e}")

except pymysql.MySQLError as e:
    print(f"数据库连接或查询出错: {e}")
except Exception as e:
    print(f"发生未知错误: {e}")
finally:
    # 关闭数据库连接
    if 'connection' in locals() and connection.open:
        cursor.close()
        connection.close()
        print("\n数据库连接已关闭。")
